package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MOrg;
import org.compiere.model.MPeriod;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import extend.org.compiere.utils.Util;

public class MM_Form_410 extends SvrProcess {
	//Form_410_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	int p_C_Period_ID;
	/** */
	String ResultPath;
	Date StartDate;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("C_Period_ID")) 
				p_C_Period_ID  = para[i].getParameterAsInt();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_410_";
		Integer pathI = 0;
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xls");
			try { 
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(";
					ResultPath = ResultPath.substring(0, 10) + (++pathI).toString() + ")";  
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" +(++pathI).toString() + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));
		
		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//New Cell Style 
		CellStyle cs = wb.createCellStyle();
		cs.setBorderBottom((short)1);
		cs.setBorderLeft((short)1);
		cs.setBorderRight((short)1);
		cs.setBorderTop((short)1);
		cs.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		cs.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		
		//Header
		sheet.getRow(7).getCell(0).setCellValue(MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(18).getCell(13).setCellValue((new MPeriod(m_ctx, p_C_Period_ID, get_TrxName()).getName()));
		//Positions
		int row = 24;
		PreparedStatement pspos = null;
		ResultSet rspos = null;
		String sqlpos = "SELECT 	i.MovementDate " + 
									" , p.Value AS ProductCode " + 
									" , p.Name AS Product " + 
									" , fa.AmtAcctCr / il.QtyInternalUse AS ColPrice " + 
									" , il.QtyInternalUse AS Quantity " + 
									" , fa.AmtAcctCr AS Summa " + 
									" , ch.Name AS Goal " + 
									" , l.Value AS MOL " + 
						" FROM Fact_Acct fa " + 
						" INNER JOIN M_Inventory i ON i.M_Inventory_ID = fa.Record_ID " + 
						" INNER JOIN M_InventoryLine il ON il.M_InventoryLine_ID = fa.Line_ID " + 
						" INNER JOIN M_Product p ON p.M_Product_ID = il.M_Product_ID " + 
						" INNER JOIN C_Charge ch ON ch.C_Charge_ID = il.C_Charge_ID " + 
						" INNER JOIN M_Locator l ON l.M_Locator_ID = il.M_Locator_ID " + 
						" INNER JOIN C_ElementValue ev ON ev.C_ElementValue_ID = fa.Account_ID " + 
						" WHERE fa.AD_Table_ID = 321 " + 
									" AND fa.Counter < fa.Reference " + 
									" AND ev.Value LIKE '131%' " + 
									" AND i.DocStatus != 'VO' AND i.DocStatus != 'RE' " + 
									" AND i.MovementDate BETWEEN '" + (new MPeriod(m_ctx, p_C_Period_ID, get_TrxName()).getStartDate()) + "' AND '" + (new MPeriod(m_ctx, p_C_Period_ID, get_TrxName()).getEndDate()) + "' " + 
						" UNION ALL  " + 
						" SELECT 	(NULL)::timestamp AS MovementDate " + 
									" , p.Value AS ProductCode " + 
									" , p.Name AS Product " + 
									" , (NULL)::NUMERIC AS ColPrice " + 
									" , sum(il.QtyInternalUse) AS Quantity " + 
									" , sum(fa.AmtAcctCr) AS Summa " + 
									" , (NULL)::CHARACTER VARYING AS Goal " + 
									" , (NULL)::CHARACTER VARYING AS MOL " + 
						" FROM Fact_Acct fa " + 
						" INNER JOIN M_Inventory i ON i.M_Inventory_ID = fa.Record_ID " + 
						" INNER JOIN M_InventoryLine il ON il.M_InventoryLine_ID = fa.Line_ID " + 
						" INNER JOIN M_Product p ON p.M_Product_ID = il.M_Product_ID " + 
						" INNER JOIN C_Charge ch ON ch.C_Charge_ID = il.C_Charge_ID " + 
						" INNER JOIN M_Locator l ON l.M_Locator_ID = il.M_Locator_ID " + 
						" INNER JOIN C_ElementValue ev ON ev.C_ElementValue_ID = fa.Account_ID " + 
						" WHERE fa.AD_Table_ID = 321 " + 
									" AND fa.Counter < fa.Reference " + 
									" AND ev.Value LIKE '131%' " + 
									" AND i.DocStatus != 'VO' AND i.DocStatus != 'RE' " + 
									" AND i.MovementDate BETWEEN '" + (new MPeriod(m_ctx, p_C_Period_ID, get_TrxName()).getStartDate()) + "' AND '" + (new MPeriod(m_ctx, p_C_Period_ID, get_TrxName()).getEndDate()) + "' " + 
						" GROUP BY ProductCode, Product " + 
						" ORDER BY Product, MovementDate";
		
		try {
			pspos = DB.prepareStatement(sqlpos.toString(), get_TrxName());
			rspos = pspos.executeQuery();
			
			while (rspos.next()) {
				//shifting and merging 
				sheet.shiftRows(row, row + 28, 1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 3));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 4, 8));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 9, 13));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 14, 18));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 19, 23));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 24, 28));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 29, 33));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 34, 38));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 39, 42));
				
				R = sheet.createRow(row);
				for (int i = 0; i < 43; i++) {
					C = R.createCell(i);
					C.setCellStyle(cs);
				}
				
				sheet.getRow(row).getCell(0).setCellValue(rspos.getDate("MovementDate") == null ? "Всего" : (new SimpleDateFormat("dd.MM.yyyy").format(rspos.getDate("MovementDate"))));
				sheet.getRow(row).getCell(4).setCellValue(rspos.getString("ProductCode"));
				sheet.getRow(row).getCell(9).setCellValue(rspos.getString("Product"));
				sheet.getRow(row).getCell(14).setCellValue(rspos.getFloat("ColPrice"));
				sheet.getRow(row).getCell(19).setCellValue(rspos.getInt("Quantity"));
				sheet.getRow(row).getCell(24).setCellValue(rspos.getFloat("Summa"));
				sheet.getRow(row).getCell(29).setCellValue(rspos.getString("Goal"));
				sheet.getRow(row).getCell(34).setCellValue(rspos.getString("MOL"));
				
				row++;
			}			
		} catch (Exception e) {
			log.log(Level.SEVERE, sqlpos.toString(), e);
		} finally {
			DB.close(rspos, pspos);
			rspos = null;
			pspos = null;
		}
		
		//---------------------------------------------------------------
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		// Open the file with the application associated
		Util.launchFile(new File(ResultPath));

		return Msg.translate(m_ctx, "Success");
	}

}
